﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Mono.Data.SqliteClient;
using NLog;

namespace Tab2Sql.Classes.db
{
    /// <summary>
    /// 
    /// </summary>
    class historyDB
    {
        private static Logger logger = LogManager.GetCurrentClassLogger ();
        /// <summary>
        /// Default constructor for history object. 
        /// </summary>
        public historyDB ()
        {
        	try {
        		InternalCounter = 0;
        		string historyPath = Properties.Settings.Default.historyDBPath;
        		if (historyPath.Length < 3) {
        			historyPath = System.IO.Path.Combine (Environment.CurrentDirectory, "historyDB.sqlite");
        		}
        		sdb = new SqliteDB (historyPath);
        		if (!sdb.Exists ("fileLog")) {
        			sdb.ExecuteScalar (CreateFileLogStatement);
        		}
        		if (!sdb.Exists ("commandLog")) {
        			sdb.ExecuteScalar (CreateCommandLogStatement);
        		}
        		if (!sdb.Exists ("commandBody")) {
        			sdb.ExecuteScalar (CreateCommandLogStatement);
        		}
        		if (!sdb.Exists ("uniqueFiles")) {
        			sdb.ExecuteScalar (CreateUniqueFileLog);
        		}
				
        		commandLogSize = -1;
        	}
			catch (Exception ex)
			{
        		logger.Error (ex.Message);
			}
        }
        internal SqliteDB sdb;
        /// <summary>
        ///
        /// </summary>
        public int InternalCounter;
        /// <summary>
        ///
        /// </summary>
        public string LastStatement;

        private int commandLogSize;
        /// <summary>
        /// How many commands currently exist within the commandLog table?
        /// </summary>
        public int CommandLogSize
        {
            get { return commandLogSize; }
        }

        /// <summary>
        /// Adds another entry into the fileLog table. 
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public int AddFileEntry(string fileName)
        {
            int nRows = 0;
            string comment = "NoComment";
            string s = string.Format("insert into fileLog ( filePath,nRows, comment) VALUES ('{0}', {1},'{2}');",fileName,nRows,comment);
            nRows =  sdb.ExecuteSql(s);
            return nRows;
        }
        /// <summary>
        /// Returns entire FileEntries table.
        /// </summary>
        /// <returns></returns>
        public string ReadFileEntries()
        {
            string s = "SELECT * FROM fileLog";
            return sdb.RenderAsTextSql(s);
        }
        /// <summary>
        /// What sort of resultset should we return? If Non-Select (i.e. an Update, Delete, Insert or similar statement has been issued
        /// then the only response we need is a count of affected rows. If it's a select query then we need the full resultset. 
        /// </summary>
        public enum statementClass
        {
            Unknown,
            Select,
            NonSelect
        }

        private bool isStatementValid (string inputSql)
        {
        	string mangledSql = inputSql.Trim().ToLowerInvariant ();
   
        	
        	if (mangledSql.Length < 10 || mangledSql.Length>9000000)
			{
        		return false;
        	}
        	if (mangledSql.Contains ("select") == false && mangledSql.Contains ("update") == false && mangledSql.Contains ("insert") == false && mangledSql.Contains ("delete") == false && mangledSql.Contains ("create") == false && mangledSql.Contains ("drop") == false)
			{
        		return false;
        	}
        	return true;
        }
		
		

        /// <summary>
        /// Adhoc Sql query. Response is always string but this could be a single row showing affected rows or a text representation of a datatable. 
        /// </summary>
        /// <param name="SqlStatement"></param>
        /// <returns></returns>
        public string Adhoc (string SqlStatement)
        {
        	bool isvalid = isStatementValid (SqlStatement);
        	string endResult = "";
        	if (!isvalid)
			{
        		logger.Info ("Adhoc Statement failed to meet requirements. SqlStatement was {0}", DGen.DText.Left (SqlStatement, 255).Replace ("\n", " "));
        		return "Sorry, an error has occurred. See the error log for more info.";
        	}
        	if (checkSpecial (SqlStatement))
			{
        		return doSpecial (SqlStatement);
        	
			}
   
        	if (checkSpecial (SqlStatement) == false)
			{
        		statementClass sClass = getClass (SqlStatement);
        		if (sClass == statementClass.Select || sClass == statementClass.Unknown)
				{
        			try {
        				endResult = sdb.RenderAsTextSql (SqlStatement);
        				return endResult;
        			}
					catch (Exception ex) 
						{
        				logger.Error ("Adhoc Error. SqlStatement was {1}. Message : {0}", ex.Message, DGen.DText.Left (SqlStatement, 255).Replace ("\n", " "));
        				return "Sorry, an error has occurred. See the error log for more info.";
        			}
        		}
        		endResult = string.Format ("\n{0} row(s) affected.\n", sdb.ExecuteSql (SqlStatement));
        		if (sdb.LastRowCount > 1 || SqlStatement.ToLower ().StartsWith ("create")) {
        			AddStatement (SqlStatement, true);
        		}
        		
        			
			}

				return endResult;
        
			
			
        }
        private statementClass getClass(string SqlStatement)
        {
            string tmp = SqlStatement.ToLower().Trim();
            if (tmp.StartsWith("select") )
            {
                return statementClass.Select;
            }
            if (tmp.StartsWith("update") || tmp.StartsWith("delete") || tmp.StartsWith("insert"))
            {
                return statementClass.NonSelect;
            }
            else { return statementClass.Unknown; }
        }
        private enum historyCommands 
        {
            viewHistory,
            clearScreen,
            viewSchema,
            viewFullSchema,
            saveLog,
            deleteDuplicates,
            help
        }
        private Dictionary<string, historyCommands> getSystemCommands()
        {
            Dictionary<string, historyCommands> systemCommands = new Dictionary<string, historyCommands>();
            systemCommands.Add("history", historyCommands.viewHistory);
            systemCommands.Add("cls", historyCommands.clearScreen);
            systemCommands.Add("schema", historyCommands.viewSchema);
            systemCommands.Add("fullschema", historyCommands.viewFullSchema);
            systemCommands.Add("save", historyCommands.saveLog);
            systemCommands.Add("deletedupes", historyCommands.deleteDuplicates);
            systemCommands.Add("help", historyCommands.help);
            return systemCommands;
        }
        private void refreshLogSize()
        {
            commandLogSize = sdb.GetRowCount("commandLog") - 1;
        }
        private string CreateFileLogStatement = "CREATE  TABLE \"fileLog\" (\"id\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \"filePath\" VARCHAR(255), \"LMD\" DATETIME NOT NULL  DEFAULT CURRENT_TIMESTAMP, \"NRows\" INTEGER NOT NULL  DEFAULT 0, \"Comment\" TEXT)";
        private string CreateCommandLogStatement = "CREATE  TABLE  IF NOT EXISTS \"commandLog\" (\"ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \"LMD\" DATETIME NOT NULL  DEFAULT CURRENT_TIMESTAMP, \"Statement\" VARCHAR, \"RetainCode\" INTEGER NOT NULL  DEFAULT 1, \"Checksum\" VARCHAR NOT NULL  DEFAULT '-1')";
        private string CreateCommandBody = "CREATE TABLE  IF NOT EXISTS \"commandBody\" (\"ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \"CommandID\" INTEGER NOT NULL , \"SequenceID\" INTEGER NOT NULL , \"CommandText\" TEXT)";
		private string CreateUniqueFileLog = "CREATE VIEW IF NOT EXISTS uniqueFiles AS SELECT DISTINCT filePath FROM fileLog LIMIT 100";
		


		
		
        /// <summary>
        /// Inserts additional record into fileLog table with the file. 
        /// This table populates the File=>Recent menu in tab2sql.
        /// </summary>
        /// <param name="defaultFileName"></param>
        /// <returns></returns>
        private Dictionary<string, historyCommands> SystemCommands
        {
            get { return getSystemCommands(); }
        }
        private bool checkSpecial(string SqlStatement)
        {
            string t = SqlStatement.ToLower().Trim();
            if (SystemCommands.Keys.Contains(t))
            {
                return true;
            }
            else { return false; }
        }
        private string doSpecial(string t)
        {
             t = t.ToLower().Trim();
            historyCommands command;
            bool isPass = SystemCommands.TryGetValue(t, out command);
            if (!isPass)
            {
                return String.Empty;
            }
            switch (command)
            {
                case historyCommands.viewHistory:
                    return viewHistory();
                case historyCommands.saveLog:
                    return saveLog();
                case historyCommands.clearScreen:
                    return "CLS";
                case historyCommands.viewSchema:
                    return viewSchema(false);
                case historyCommands.viewFullSchema:
                    return viewSchema(true);
                case historyCommands.deleteDuplicates:
                    return deleteDuplicates();
                case historyCommands.help:
                    return showHelp();
                default:
                    break;
            }
            return String.Empty;
        }

        private string showHelp()
        {
            StringBuilder helpText = new StringBuilder();
            foreach (KeyValuePair<string,historyCommands> kvp in SystemCommands)
            {
                helpText.AppendFormat("{0} - {1}", kvp.Key, kvp.Value);

            }
            return helpText.ToString();
        }
        private string saveLog ()
        {
        	try {
        		string s = "SELECT * FROM commandLog";
        		string tmp = DGen.DText.ObjectToString.DataTableToString (sdb.RenderSql (s));
        		string tmpFn = DGen.DFile.uniqueFileName (".txt", "commandLog", true);
        		DGen.DFile.WriteToFile (tmpFn, tmp);
        		return tmpFn;
        	}
			catch (Exception ex) 
			{
        		logger.Error (ex);
        		return "Fail";
			}
        }
        private string deleteDuplicates()
        {
            string s = @"delete from commandlog where id in ( select last_occurs from 
                                                (
                                                select 
                                                        statement,
                                                        min(id) as first_occurs,
                                                        max(id) as last_occurs
                                                    from vHistory
                                                group by statement
                                                having count(*) >1
                                                    ) d1
                                                    where d1.first_occurs<>d1.last_occurs
                                                )";
            int rows = sdb.ExecuteSql(s);
            return string.Format("\n{0} row(s) affected.\n", rows); 
        }
        private string viewSchema(bool showFull)
        {
            return sdb.DisplaySchema(showFull);
        }
        private string viewHistory()
        {
            string s = "SELECT ID, Statement, RetainCode  FROM commandLog WHERE RetainCode>0";
            DataTable dt = sdb.RenderSql(s);
            StringBuilder sb = new StringBuilder(dt.Rows.Count * 10);
            sb.AppendLine("\nID    Statement                                                                                                                  RetainCode");
            sb.AppendLine("===========================================================================================================================================");
            foreach (DataRow dr in dt.Rows)
            {
                string tmpHolder = dr["Statement"].ToString();
                tmpHolder = tmpHolder.Replace("\n", " ");
                tmpHolder = tmpHolder.PadRight(120);
                sb.AppendFormat("{0}. {1}   {2}\n", dr["ID"].ToString().PadRight(4), tmpHolder, dr["RetainCode"]);
            }
            return sb.ToString();
        }
        /// <summary>
        /// Reads file list as single column.
        /// </summary>
        /// <returns></returns>
        public List<string> ReadFileEntryList()
        {
            List<string> endResult = new List<string>();
            endResult = sdb.GetSingleColumnSql("SELECT filePath FROM uniqueFiles");
            return endResult;
        }
        /// <summary>
        /// Creates another record in the commandLog table.
        /// </summary>
        /// <param name="statementText"></param>
        /// <param name="tentative"></param>
        /// <returns>-100 = Statement too large</returns>
        public int AddStatement (string statementText, bool tentative)
        {
        	if (statementText.Length > 10000)
            {
        		return -100;
        	}
        	int nRows = 0;
     
            string checksum = DGen.DCrypt.DiyChecksum (DGen.DText.RemoveWhiteSpace (statementText));
        	string s = string.Format ("insert into commandLog ( Statement, Checksum) VALUES (  '{0}','{1}'  );", statementText, checksum);
        	try {
        		nRows = sdb.ExecuteSql (s);
        		LastStatement = sdb.LastStatement;
        	}
			catch (Exception ex) 
			{
        		logger.Error ("Error caused by Sql Statement : {0}. Erorr message : {1}", s,ex.Message);
        		nRows = -1;
			}
            return nRows;
        }
        /// <summary>
        /// Valid directions of travel in commandLog navigation.
        /// </summary>
        public enum Direction { Previous, Next }
        /// <summary>
        /// Returns single statement based on direction requested. If direction is NEXT and there are no more entries then the current value is returned.
        /// </summary>
        /// <param name="direction"></param>
        /// <returns></returns>
        public string StatementLoad (Direction direction)
        {
            int RequestedCounter = 0;
            if (direction == Direction.Next)
            {
                RequestedCounter = InternalCounter + 1;
            }
            if (direction == Direction.Previous)
            {
                RequestedCounter = InternalCounter - 1;
            }
            if (RequestedCounter < 0)
            {
                RequestedCounter = 0;
            }
            if (RequestedCounter > commandLogSize)
            {
                refreshLogSize ();
                if (RequestedCounter > commandLogSize)
                {
                    RequestedCounter = commandLogSize;
                }
            }
            string s = string.Format ("SELECT Statement FROM commandLog LIMIT {0},1", RequestedCounter);
            int nRows = 0;
            string endResult = sdb.ExecuteScalar (s);
            nRows = sdb.LastRowCount;
            if (nRows < 0)
            {
                logger.Error ("Less than 0 rows returned by Sql Statement : {0}", s);
            }
            else { InternalCounter = RequestedCounter; }
            LastStatement = sdb.LastStatement;
            return endResult;
        }

    }
}
